import configparser

import mysql.connector

from group_by_page import group_by_page_id


def get_db_config():
    """从配置文件读取数据库连接信息"""
    config = configparser.ConfigParser()
    config.read('config.ini', encoding='utf-8')
    return {
        'host': config.get('database', 'host'),
        'user': config.get('database', 'user'),
        'password': config.get('database', 'password'),
        'database': config.get('database', 'database')
    }


def get_connection():
    db_config = get_db_config()
    connection = mysql.connector.connect(**db_config)
    if connection.is_connected():
        return connection

    return None


def get_data_sql(connection, book_id):
    """从数据库中获取数据，并进行分页操作"""
    cursor = connection.cursor(dictionary=True)

    # 查询annotation指定book_id的文字列表
    annotation_sql = """
SELECT
    a.id annotation_id,
    a.book_id,
    a.page_id,
    a.word,
    a.word_image_path,
    a.recognition_rate,
    a.word_coordinates,
    pg.page_image_path,
    b.reading_sequence,
    b.layout_direction
FROM annotation AS a
         JOIN book_page AS pg ON pg.id = a.page_id
         JOIN book AS b ON b.id = a.book_id
WHERE a.book_id = %s
ORDER BY a.page_id;
    """

    cursor.execute(annotation_sql, (book_id,))
    annotation_result = cursor.fetchall()

    # 查询book指定book_id的book_info
    book_sql = """
    SELECT title,author,layout_direction,reading_sequence
    FROM book
    WHERE id = %s
    """
    cursor.execute(book_sql, (book_id,))
    book_result = cursor.fetchall()
    book_info = book_result[0]

    # 根据page_id分组
    pages_content_list = group_by_page_id(results=annotation_result)

    # 返回以page_id为一个索引的列表，方便后面用for就可以直接以page_id为单位排序
    data = []
    for page_content in pages_content_list:
        # 设置基础信息
        item = {
            'book_id': book_id,
            'title': book_info['title'],
            'author': book_info['author'],
            'page_id': page_content['page_id'],
            'layout_direction':book_info['layout_direction'],
            'reading_sequence':book_info['reading_sequence'],
            'page_image_path': page_content['page_image_path'],
            'page_word': page_content['word_info'],
        }

        data.append(item)

    cursor.close()
    connection.close()

    return data


def get_layout_direction_mode(book_id):
    if get_connection():
        connection = get_connection()
        cursor = connection.cursor()

        book_sql = """
        SELECT layout_direction FROM 1 WHERE id =%s
        """

        cursor.execute(book_sql, (book_id,))
        layout_direction = cursor.fetchall()

        return layout_direction

    return None
